CREATE TABLESPACE USERS 
    DATAFILE 
        'USERS.DBF' SIZE 104857600 AUTOEXTEND ON NEXT 1280 MAXSIZE 5368709120 
    BLOCKSIZE 8192 
    LOGGING 
    DEFAULT NOCOMPRESS 
    ONLINE 
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

CREATE UNDO TABLESPACE UNDO 
    DATAFILE 
        'UNDO.DBF' SIZE 94371840 AUTOEXTEND ON NEXT 640 MAXSIZE 524288000 
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE 
    RETENTION NOGUARANTEE;

CREATE TEMPORARY TABLESPACE TEMP 
    TEMPFILE 
        'TEMP.DBF' SIZE 20971520 AUTOEXTEND ON NEXT 80 MAXSIZE 34359721984 
    EXTENT MANAGEMENT LOCAL;

CREATE TABLESPACE SYSTEM 
    DATAFILE 
        'SYSTEM.DBF' SIZE 356515840 AUTOEXTEND ON NEXT 1280 MAXSIZE 629145600 
    BLOCKSIZE 8192 
    LOGGING 
    DEFAULT NOCOMPRESS 
    ONLINE 
    SEGMENT SPACE MANAGEMENT MANUAL 
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

CREATE TABLESPACE SYSAUX 
    DATAFILE 
        'SYSAUX.DBF' SIZE 471859200 AUTOEXTEND ON NEXT 1280 MAXSIZE 34359721984 
    BLOCKSIZE 8192 
    LOGGING 
    DEFAULT NOCOMPRESS 
    ONLINE 
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

CREATE TABLE TASK_LOG 
(
  TSL_ID NUMBER NOT NULL 
, TSL_TS_ID NUMBER NOT NULL 
, TSL_DATE DATE NOT NULL 
, TSL_CREATED_BY VARCHAR2(255 BYTE) NOT NULL 
, TSL_STATUS NUMBER 
, TSL_NOTE VARCHAR2(255 BYTE) 
, CONSTRAINT TASK_LOG_PK PRIMARY KEY 
  (
    TSL_ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE TASK 
(
  TS_ID NUMBER NOT NULL 
, TS_PROJECT_ID VARCHAR2(10 BYTE) NOT NULL 
, TS_TITLE VARCHAR2(255 BYTE) NOT NULL 
, TS_STATUS NUMBER NOT NULL 
, TS_CREATED_DATE DATE NOT NULL 
, TS_CREATED_BY VARCHAR2(255 BYTE) NOT NULL 
, TS_EXPIRATION_DATE DATE NOT NULL 
, CONSTRAINT TASK_PK PRIMARY KEY 
  (
    TS_ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE PS_TXN 
(
  ID NUMBER(20, 0) NOT NULL 
, PARENTID NUMBER(20, 0) 
, COLLID NUMBER(10, 0) NOT NULL 
, CONTENT BLOB 
, CREATION_DATE DATE DEFAULT sysdate 
, CONSTRAINT PS_TXN_PK PRIMARY KEY 
  (
    COLLID 
  , ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
) 
LOB (CONTENT) STORE AS SYS_LOB0000014348C00004$$ 
( 
  ENABLE STORAGE IN ROW 
  CHUNK 8192 
  RETENTION 
  CACHE 
  LOGGING 
  STORAGE 
  ( 
    INITIAL 65536 
    MINEXTENTS 1 
    MAXEXTENTS 2147483645 
    BUFFER_POOL DEFAULT 
  )  
);

CREATE TABLE PROJECT_STATUS 
(
  PST_ID NUMBER NOT NULL 
, PST_DESC VARCHAR2(20 BYTE) NOT NULL 
, CONSTRAINT PROJECT_STATUS_PK PRIMARY KEY 
  (
    PST_ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE PROJECT_REV 
(
  PR_ID VARCHAR2(10 BYTE) NOT NULL 
, PR_CURRVER NUMBER NOT NULL 
, CONSTRAINT PROJECT_REV_PK PRIMARY KEY 
  (
    PR_ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE PROJECT 
(
  PR_ID VARCHAR2(10 BYTE) NOT NULL 
, PR_DESCRIPTION VARCHAR2(255 BYTE) 
, PR_MODIFIED_DATE DATE 
, PR_MODIFIED_BY VARCHAR2(255 BYTE) 
, PR_REV_ID NUMBER NOT NULL 
, STATUS NUMBER(*, 0) 
, PR_CUSTOMER_ID VARCHAR2(20 BYTE) 
, CONSTRAINT PROJECT_PK PRIMARY KEY 
  (
    PR_ID 
  , PR_REV_ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE PAYMENT_STATUS 
(
  PYS_ID NUMBER NOT NULL 
, PYS_DESCRIPTION VARCHAR2(20 BYTE) NOT NULL 
, CONSTRAINT PAYMENT_STATUS_PK PRIMARY KEY 
  (
    PYS_ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE PAYMENT 
(
  PY_ID NUMBER(*, 0) NOT NULL 
, PY_TITLE VARCHAR2(255 BYTE) NOT NULL 
, PY_STATUS NUMBER(*, 0) NOT NULL 
, PY_CURRENCY VARCHAR2(3 BYTE) NOT NULL 
, PY_AMOUNT NUMBER NOT NULL 
, PY_CREATED_DATE DATE NOT NULL 
, PY_CREATED_BY VARCHAR2(255 BYTE) NOT NULL 
, PY_INVOICE_ID VARCHAR2(20 BYTE) 
, PY_NOTIFICATION_DATE DATE NOT NULL 
, PY_NOTES VARCHAR2(255 BYTE) 
, PY_TEXT CLOB 
, PY_PROJECT_ID VARCHAR2(10 BYTE) 
, CONSTRAINT PAYMENT_PK PRIMARY KEY 
  (
    PY_ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
) 
LOB (PY_TEXT) STORE AS SYS_LOB0000014356C00011$$ 
( 
  ENABLE STORAGE IN ROW 
  CHUNK 8192 
  RETENTION 
  NOCACHE 
  LOGGING 
  STORAGE 
  ( 
    INITIAL 65536 
    MINEXTENTS 1 
    MAXEXTENTS 2147483645 
    BUFFER_POOL DEFAULT 
  )  
);

CREATE TABLE GANTT_ELEMENT 
(
  GTE_ID NUMBER NOT NULL 
, GTE_TYPE VARCHAR2(255 BYTE) 
, GTE_START_DATE DATE NOT NULL 
, GTE_END_DATE DATE NOT NULL 
, GTE_DEPENDENT_GTE_ID NUMBER 
, GTE_DESCRIPTION VARCHAR2(255 BYTE) NOT NULL 
, GTE_DEPENDENT_GTE_TYPE VARCHAR2(255 BYTE) 
, GTE_SUBGTE_ID NUMBER 
, GTE_PERCENTUAGE_COMPLETION VARCHAR2(20 BYTE) 
, GTE_CREATED_DATE DATE NOT NULL 
, GTE_CREATED_BY VARCHAR2(255 BYTE) NOT NULL 
, GTE_PRIORITY NUMBER 
, GTE_PROJECT_ID VARCHAR2(10 BYTE) NOT NULL 
, CONSTRAINT GANTT_ELEMENT_PK PRIMARY KEY 
  (
    GTE_ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE CUSTOMER 
(
  CST_ID VARCHAR2(20 BYTE) NOT NULL 
, CST_NAME VARCHAR2(20 BYTE) NOT NULL 
, CONSTRAINT CUSTOMER_PK PRIMARY KEY 
  (
    CST_ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

ALTER TABLE TASK_LOG
ADD CONSTRAINT TASK_LOG_TASK_FK1 FOREIGN KEY
(
  TSL_TS_ID 
)
REFERENCES TASK
(
  TS_ID 
)
ENABLE;

ALTER TABLE TASK
ADD CONSTRAINT TASK_PROJECT_REV_FK1 FOREIGN KEY
(
  TS_PROJECT_ID 
)
REFERENCES PROJECT_REV
(
  PR_ID 
)
ENABLE;

ALTER TABLE PROJECT_REV
ADD CONSTRAINT PROJECT_REV_PROJECT_FK1 FOREIGN KEY
(
  PR_ID 
, PR_CURRVER 
)
REFERENCES PROJECT
(
  PR_ID 
, PR_REV_ID 
)
ENABLE;

ALTER TABLE PROJECT
ADD CONSTRAINT PROJECT_CUSTOMER_FK1 FOREIGN KEY
(
  PR_CUSTOMER_ID 
)
REFERENCES CUSTOMER
(
  CST_ID 
)
ENABLE;

ALTER TABLE PROJECT
ADD CONSTRAINT PROJECT_PROJECT_STATUS_FK1 FOREIGN KEY
(
  STATUS 
)
REFERENCES PROJECT_STATUS
(
  PST_ID 
)
ENABLE;

ALTER TABLE PAYMENT
ADD CONSTRAINT PAYMENT_PAYMENT_STATUS_FK1 FOREIGN KEY
(
  PY_STATUS 
)
REFERENCES PAYMENT_STATUS
(
  PYS_ID 
)
ENABLE;

ALTER TABLE PAYMENT
ADD CONSTRAINT PAYMENT_PROJECT_REV_FK1 FOREIGN KEY
(
  PY_PROJECT_ID 
)
REFERENCES PROJECT_REV
(
  PR_ID 
)
ENABLE;

ALTER TABLE GANTT_ELEMENT
ADD CONSTRAINT GANTT_ELEMENT_GANTT_ELEME_FK1 FOREIGN KEY
(
  GTE_PROJECT_ID 
)
REFERENCES PROJECT_REV
(
  PR_ID 
)
ENABLE;

CREATE SEQUENCE TASK_LOG_ID_SEQ INCREMENT BY 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 CACHE 20;

CREATE SEQUENCE TASK_ID_SEQ INCREMENT BY 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 CACHE 20;

CREATE SEQUENCE PY_ID_SEQ INCREMENT BY 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 CACHE 20;

CREATE SEQUENCE PS_TXN_SEQ INCREMENT BY 50 MAXVALUE 999999999999999999999999999 MINVALUE 1 CACHE 20;

CREATE SEQUENCE PR_REV_ID_SEQ INCREMENT BY 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 CACHE 20;

CREATE SEQUENCE GANTT_ELEMENT_ID_SEQ INCREMENT BY 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 CACHE 20;

CREATE TRIGGER PR_REV_ID_TRG BEFORE INSERT ON PROJECT 
FOR EACH ROW 
BEGIN
  <<COLUMN_SEQUENCES>>
  BEGIN
    IF :NEW.PR_REV_ID IS NULL THEN
      SELECT PR_REV_ID_SEQ.NEXTVAL INTO :NEW.PR_REV_ID FROM DUAL;
    END IF;
  END COLUMN_SEQUENCES;
END;
/
